package com.renjun.smweb.service.AdmissionService.impl;

import com.alibaba.fastjson.JSONObject;
import com.renjun.smweb.commons.componet.BaseDaoComponent;
import com.renjun.smweb.commons.componet.FileIOComponent;
import com.renjun.smweb.commons.enums.EOperators;
import com.renjun.smweb.commons.enums.EStandTables;
import com.renjun.smweb.commons.global.DataDict;
import com.renjun.smweb.commons.global.DataMap;
import com.renjun.smweb.commons.global.FileInfoPo;
import com.renjun.smweb.commons.util.*;
import com.renjun.smweb.dao.AdmissionDao;
import com.renjun.smweb.dao.bean.InsertParams;
import com.renjun.smweb.dao.bean.Parameter;
import com.renjun.smweb.dao.bean.QueryParams;
import com.renjun.smweb.dao.bean.UpdateParams;
import com.renjun.smweb.dao.pojo.LoginInfo;
import com.renjun.smweb.service.AdmissionService.AdmissionService;
import com.renjun.smweb.service.CommonService.CommonService;
import com.itextpdf.text.DocumentException;
import com.renjun.smweb.commons.componet.BaseDaoComponent;
import com.renjun.smweb.commons.global.DataMap;
import com.renjun.smweb.commons.global.FileInfoPo;
import com.renjun.smweb.dao.bean.InsertParams;
import com.renjun.smweb.dao.bean.Parameter;
import com.renjun.smweb.exceptions.BaseException;
import com.renjun.smweb.exceptions.FileTypeNotFoundException;
import com.renjun.smweb.service.AdmissionService.AdmissionService;
import org.apache.commons.io.FileUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.renjun.smweb.commons.util.ResponseUtils.createErrorResponseBody;
import static com.renjun.smweb.commons.util.ResponseUtils.createSuccessResponseBody;
import static com.renjun.smweb.service.AdmissionService.impl.ExpluquNotice.createExpluquNotice;

/**
 * Created by RenJun on 2017/2/21.
 */
@Service
public class AdmissionServiceImpl implements AdmissionService {
    @Autowired
    private BaseDaoComponent baseDaoComponent;

    @Resource
    private AdmissionDao admissionDao;

    @Resource
    private CommonService commonService;

    /**
     * 报名学生录取列表M7-11
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope}
     * @return [{id,bmxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    @Override
    public JSONObject queryXXDJXSList(Map<String, Object> params) throws SQLException, BaseException {
        List<Map<String, Object>> result = queryXXDJXSListMain(params);
        JSONObject rs = createSuccessResponseBody("列表查看成功", result);
        return rs;
    }

    private List<Map<String, Object>> queryXXDJXSListMain(Map<String, Object> params) throws SQLException, BaseException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        String stuinfoTableName = loginInfo.getStuinfotablename();
        String selectViewName = getv_m711_xxdjxslist_hm(stuinfoTableName);
        QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
        String id = (String) params.remove("id");
        if ("3".equals(loginInfo.getRoletype())) {//只有学校有权限
            params.put("bmxx", loginInfo.getSchoolcode());
            if (!BaseChecks.hasEmptyStr(id)) {
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", EOperators.包含, id.split(",")));
            } else {
                String name = (String) params.remove("xm");
                if (!BaseChecks.hasEmptyStr(name)) {//模糊查询
                    //判断是否为中文
                    if (BaseChecks.isChineseChar(name)) {
                        //是，空格全部替换
                        name = name.replaceAll(" ", "");
                    } else {
                        //否，替换两端空格
                        name = BaseChecks.bothEndsStr(name);
                    }
                    qeuryAdmissionList.addQueryParams(Parameter.createParameter("xm", EOperators.类似, name));
                }
                if (params.get("hksf") != null && !"".equals(params.get("hksf"))) {
                    if ("1".equals(params.get("hksfscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hksf", EOperators.等于, params.get("hksf")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hksf", EOperators.不等于, params.get("hksf")));
                    }
                    params.remove("hksf");
                }
                params.remove("hksfscope");
                if (params.get("hkcity") != null && !"".equals(params.get("hkcity"))) {
                    if ("1".equals(params.get("hkcityscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkcity", EOperators.等于, params.get("hkcity")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkcity", EOperators.不等于, params.get("hkcity")));
                    }
                    params.remove("hkcity");
                }
                params.remove("hkcityscope");
                if (params.get("hkqx") != null && !"".equals(params.get("hkqx"))) {
                    if ("1".equals(params.get("hkqxscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkqx", EOperators.等于, params.get("hkqx")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkqx", EOperators.不等于, params.get("hkqx")));
                    }
                    params.remove("hkqx");
                }
                params.remove("hkqxscope");
            }
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
        Map<String, Object> map = new DataMap<>();
        for (Map.Entry<String, Object> m : params.entrySet()) {
            if (m.getValue() != null && !"".equals(m.getValue())) map.put(m.getKey(), m.getValue());
        }
        qeuryAdmissionList.addQueryParamsByMap(map);
        qeuryAdmissionList.printSelf();
        try {
            qeuryAdmissionList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
        return result;
    }

    /**
     * 导出报名学生录取列表M7-112
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope}
     * @return [{id,bmxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    public JSONObject expQueryXXDJXSList(Map<String, Object> params) throws SQLException, BaseException, IOException {
        params.remove("limit");
        params.remove("offset");
        List<Map<String, Object>> result = queryXXDJXSListMain(params);
        // 年级，姓名，性别，证件类型，证件号码，出生日期，户口省份，户口区县，入园依据，住房性质，登记状态，录取状态
        // ID,NJDM,XM,XB,SFZLX,SFZJH,CSRQ,HKSF,HKQX,RYYJ,ZFXZ,DJLX,LQZT,CHECKSTATUS
        //NUMBER:序号,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态
        FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, "xlsx", "招录管理-报名学生录取管理");
        String absPath = fileInfoPo.getFileAbsPath();
        String virPath = fileInfoPo.getFileVirPath();
        Map<String, Object> filePath = new HashMap<>();
        filePath.put("url", virPath);
        ExportExcel eh = ExportExcel.createExportExcel(absPath, "{NUMBER:序号,BMXXMC:报名学校,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态}");
        for (int i = 0, len = result.size(); i < len; i++) {
            result.get(i).put("NUMBER", i + 1);
            try {
                result.get(i).put("NJDM", DataDict.getChineseValueByCode(EStandTables.年级表, result.get(i).get("NJDM").toString()));
            } catch (Exception e) {
                result.get(i).put("NJDM", "");
            }
            try {
                result.get(i).put("XB", DataDict.getChineseValueByCode(EStandTables.性别表, result.get(i).get("XB").toString()));
            } catch (Exception e) {
                result.get(i).put("XB", "");
            }
            try {
                result.get(i).put("SFZLX", DataDict.getChineseValueByCode(EStandTables.证件类型表, result.get(i).get("SFZLX").toString()));
            } catch (Exception e) {
                result.get(i).put("SFZLX", "");
            }
            try {
                result.get(i).put("HKSF", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKSF").toString()));
            } catch (Exception e) {
                result.get(i).put("HKSF", "");
            }
            try {
                result.get(i).put("HKQX", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKQX").toString()));
            } catch (Exception e) {
                result.get(i).put("HKQX", "");
            }
            try {
                result.get(i).put("RYYJ", DataDict.getChineseValueByCode(EStandTables.入园依据表, result.get(i).get("RYYJ").toString()));
            } catch (Exception e) {
                result.get(i).put("RYYJ", "");
            }
            try {
                result.get(i).put("ZFXZ", DataDict.getChineseValueByCode(EStandTables.住房性质表, result.get(i).get("ZFXZ").toString()));
            } catch (Exception e) {
                result.get(i).put("ZFXZ", "");
            }
            try {
                result.get(i).put("DJLX", DataDict.getChineseValueByCode(EStandTables.登记类型表, result.get(i).get("DJLX").toString()));
            } catch (Exception e) {
                result.get(i).put("DJLX", "");
            }
            try {
                result.get(i).put("LQZT", DataDict.getChineseValueByCode(EStandTables.录取状态表, result.get(i).get("LQZT").toString()));
            } catch (Exception e) {
                result.get(i).put("LQZT", "");
            }
            try {
                result.get(i).put("CHECKSTATUS", DataDict.getChineseValueByCode(EStandTables.校验状态表, result.get(i).get("CHECKSTATUS").toString()));
            } catch (Exception e) {
                result.get(i).put("CHECKSTATUS", "");
            }
        }
        eh.setCurrentData(result);
        eh.writeCurrentData();
        result.add(filePath);
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url", virPath);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("导出成功", urlMap);
        return rs;
    }

    /**
     * 录取学生名单列表M7-12
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope}
     * @return [{id,lqxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    @Override
    public JSONObject queryXXLuquList(Map<String, Object> params) throws SQLException, BaseException {
        List<Map<String, Object>> result = queryXXLuquListMain(params);
        JSONObject rs = createSuccessResponseBody("列表查看成功", result);
        return rs;
    }

    private List<Map<String, Object>> queryXXLuquListMain(Map<String, Object> params) throws SQLException, BaseException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        String stuinfoTableName = loginInfo.getStuinfotablename();
        String selectViewName = getv_m712_XXLuquList_hm(stuinfoTableName);
        QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
        String id = (String) params.remove("id");
        if ("3".equals(loginInfo.getRoletype())) {//只有学校有权限
            params.put("lqqx", loginInfo.getDistrictcode());
            params.put("lqxx", loginInfo.getSchoolcode());
            if (!BaseChecks.hasEmptyStr(id)) {
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", EOperators.包含, id.split(",")));
            } else {
                String name = (String) params.remove("xm");
                if (!BaseChecks.hasEmptyStr(name)) {//模糊查询
                    //判断是否为中文
                    if (BaseChecks.isChineseChar(name)) {
                        //是，空格全部替换
                        name = name.replaceAll(" ", "");
                    } else {
                        //否，替换两端空格
                        name = BaseChecks.bothEndsStr(name);
                    }
                    qeuryAdmissionList.addQueryParams(Parameter.createParameter("xm", EOperators.类似, name));
                }
                if (params.get("hksf") != null && !"".equals(params.get("hksf"))) {
                    if ("1".equals(params.get("hksfscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hksf", EOperators.等于, params.get("hksf")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hksf", EOperators.不等于, params.get("hksf")));
                    }
                    params.remove("hksf");
                }
                params.remove("hksfscope");
                if (params.get("hkcity") != null && !"".equals(params.get("hkcity"))) {
                    if ("1".equals(params.get("hkcityscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkcity", EOperators.等于, params.get("hkcity")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkcity", EOperators.不等于, params.get("hkcity")));
                    }
                    params.remove("hkcity");
                }
                params.remove("hkcityscope");
                if (params.get("hkqx") != null && !"".equals(params.get("hkqx"))) {
                    if ("1".equals(params.get("hkqxscope"))) {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkqx", EOperators.等于, params.get("hkqx")));
                    } else {
                        qeuryAdmissionList.addQueryParams(Parameter.createParameter("hkqx", EOperators.不等于, params.get("hkqx")));
                    }
                    params.remove("hkqx");
                }
                params.remove("hkqxscope");
            }
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
        Map<String, Object> map = new DataMap<>();
        for (Map.Entry<String, Object> m : params.entrySet()) {
            if (m.getValue() != null && !"".equals(m.getValue())) map.put(m.getKey(), m.getValue());
        }
        qeuryAdmissionList.addQueryParamsByMap(map);
        qeuryAdmissionList.printSelf();
        try {
            qeuryAdmissionList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
        return result;
    }

    /**
     * 导出录取学生名单列表M7-122
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope}
     * @return [{id,lqxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    public JSONObject expQueryXXLuquList(Map<String, Object> params) throws SQLException, BaseException, IOException {
        params.remove("limit");
        params.remove("offset");
        List<Map<String, Object>> result = queryXXLuquListMain(params);
        // 年级，姓名，性别，证件类型，证件号码，出生日期，户口省份，户口区县，入园依据，住房性质，登记状态，录取状态
        // ID,NJDM,XM,XB,SFZLX,SFZJH,CSRQ,HKSF,HKQX,RYYJ,ZFXZ,DJLX,LQZT,CHECKSTATUS
        //NUMBER:序号,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态
        FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, "xlsx", "招录管理-录取学生名单");
        String absPath = fileInfoPo.getFileAbsPath();
        String virPath = fileInfoPo.getFileVirPath();
        Map<String, Object> filePath = new HashMap<>();
        filePath.put("url", virPath);
        ExportExcel eh = ExportExcel.createExportExcel(absPath, "{NUMBER:序号,LQXXMC:录取学校,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态}");
        for (int i = 0, len = result.size(); i < len; i++) {
            result.get(i).put("NUMBER", i + 1);
            try {
                result.get(i).put("NJDM", DataDict.getChineseValueByCode(EStandTables.年级表, result.get(i).get("NJDM").toString()));
            } catch (Exception e) {
                result.get(i).put("NJDM", "");
            }
            try {
                result.get(i).put("XB", DataDict.getChineseValueByCode(EStandTables.性别表, result.get(i).get("XB").toString()));
            } catch (Exception e) {
                result.get(i).put("XB", "");
            }
            try {
                result.get(i).put("SFZLX", DataDict.getChineseValueByCode(EStandTables.证件类型表, result.get(i).get("SFZLX").toString()));
            } catch (Exception e) {
                result.get(i).put("SFZLX", "");
            }
            try {
                result.get(i).put("HKSF", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKSF").toString()));
            } catch (Exception e) {
                result.get(i).put("HKSF", "");
            }
            try {
                result.get(i).put("HKQX", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKQX").toString()));
            } catch (Exception e) {
                result.get(i).put("HKQX", "");
            }
            try {
                result.get(i).put("RYYJ", DataDict.getChineseValueByCode(EStandTables.入园依据表, result.get(i).get("RYYJ").toString()));
            } catch (Exception e) {
                result.get(i).put("RYYJ", "");
            }
            try {
                result.get(i).put("ZFXZ", DataDict.getChineseValueByCode(EStandTables.住房性质表, result.get(i).get("ZFXZ").toString()));
            } catch (Exception e) {
                result.get(i).put("ZFXZ", "");
            }
            try {
                result.get(i).put("DJLX", DataDict.getChineseValueByCode(EStandTables.登记类型表, result.get(i).get("DJLX").toString()));
            } catch (Exception e) {
                result.get(i).put("DJLX", "");
            }
            try {
                result.get(i).put("LQZT", DataDict.getChineseValueByCode(EStandTables.录取状态表, result.get(i).get("LQZT").toString()));
            } catch (Exception e) {
                result.get(i).put("LQZT", "");
            }
            try {
                result.get(i).put("CHECKSTATUS", DataDict.getChineseValueByCode(EStandTables.校验状态表, result.get(i).get("CHECKSTATUS").toString()));
            } catch (Exception e) {
                result.get(i).put("CHECKSTATUS", "");
            }
        }
        eh.setCurrentData(result);
        eh.writeCurrentData();
        result.add(filePath);
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url", virPath);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("导出成功", urlMap);
        return rs;
    }

    /**
     * 区县录取学生名单列表M7-13
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope,djxx}
     * @return [{id,djxxmc,lqxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    @Override
    public JSONObject queryQXLuquList(Map<String, Object> params) throws SQLException, BaseException {
        List<Map<String, Object>> result = CollectionUtils.converMapKeyToLowerCase(queryQXLuquListMain(params));
        JSONObject rs = createSuccessResponseBody("列表查看成功", result);
        return rs;
    }

    public List<Map<String, Object>> queryQXLuquListMain(Map<String, Object> params) throws SQLException, BaseException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        String id = (String) params.remove("id");
        List<Map<String, Object>> result = new ArrayList<>();
        if ("2".equals(loginInfo.getRoletype())) {//只有区县有权限
            params.put("lqqx", loginInfo.getDistrictcode());
            params.put("djqx", loginInfo.getDistrictcode());
            if (!BaseChecks.hasEmptyStr(id)) {
                params.put("ids",id.split(","));
                Map<String, Object> map = new DataMap<>();
                for (Map.Entry<String, Object> m : params.entrySet()) {
                    if (m.getValue() != null && !"".equals(m.getValue())) map.put(m.getKey(), m.getValue());
                }
                String stuinfoTableName = loginInfo.getStuinfotablename();
                String selectViewName = getv_m713_qxluqulist_hm(stuinfoTableName);
                map.put("selectviewname", selectViewName);
                result = admissionDao.queryQXLuquListByIds(map);
            } else {
                String name = (String) params.remove("xm");
                if (!BaseChecks.hasEmptyStr(name)) {//模糊查询
                    //判断是否为中文
                    if (BaseChecks.isChineseChar(name)) {
                        //是，空格全部替换
                        name = name.replaceAll(" ", "");
                    } else {
                        //否，替换两端空格
                        name = BaseChecks.bothEndsStr(name);
                    }
                    params.put("xm", name);
                }
                Map<String, Object> map = new DataMap<>();
                for (Map.Entry<String, Object> m : params.entrySet()) {
                    if (m.getValue() != null && !"".equals(m.getValue())) map.put(m.getKey(), m.getValue());
                }
                String stuinfoTableName = loginInfo.getStuinfotablename();
                String selectViewName = getv_m713_qxluqulist_hm(stuinfoTableName);
                map.put("selectviewname", selectViewName);
                result = admissionDao.queryQXLuquList(map);
            }
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }

        return result;
    }

    /**
     * 导出区县录取学生名单列表M7-132
     *
     * @param {limit:10,offset:0,order:"",sort:"",xm,sfzjh,sfzlx,njdm,ryyj,hksf,hkqx,djlx,lqzt,checkstatus,hksfscope,hkqxscope}
     * @return [{id,djxxmc,lqxxmc,njdm,xm,xb,sfzlx,sfzjh,csrq,hksf,hkqx,ryyj,zfxz,djlx,lqzt,checkstatus}]
     * by huamin
     */
    public JSONObject expQueryQXLuquList(Map<String, Object> params) throws SQLException, BaseException, IOException {
        params.remove("limit");
        params.remove("offset");
        List<Map<String, Object>> result = CollectionUtils.converMapKeyToLowerCase(queryQXLuquListMain(params));
        // 年级，姓名，性别，证件类型，证件号码，出生日期，户口省份，户口区县，入园依据，住房性质，登记状态，录取状态
        // ID,NJDM,XM,XB,SFZLX,SFZJH,CSRQ,HKSF,HKQX,RYYJ,ZFXZ,DJLX,LQZT,CHECKSTATUS
        //NUMBER:序号,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态
        FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, "xlsx", "招录管理-录取管理");
        String absPath = fileInfoPo.getFileAbsPath();
        String virPath = fileInfoPo.getFileVirPath();
        Map<String, Object> filePath = new HashMap<>();
        filePath.put("url", virPath);
        ExportExcel eh = ExportExcel.createExportExcel(absPath, "{NUMBER:序号,DJXXMC:登记学校,LQXXMC:录取学校,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态}");
        for (int i = 0, len = result.size(); i < len; i++) {
            result.get(i).put("NUMBER", i + 1);
            try {
                result.get(i).put("NJDM", DataDict.getChineseValueByCode(EStandTables.年级表, result.get(i).get("NJDM").toString()));
            } catch (Exception e) {
                result.get(i).put("NJDM", "");
            }
            try {
                result.get(i).put("XB", DataDict.getChineseValueByCode(EStandTables.性别表, result.get(i).get("XB").toString()));
            } catch (Exception e) {
                result.get(i).put("XB", "");
            }
            try {
                result.get(i).put("SFZLX", DataDict.getChineseValueByCode(EStandTables.证件类型表, result.get(i).get("SFZLX").toString()));
            } catch (Exception e) {
                result.get(i).put("SFZLX", "");
            }
            try {
                result.get(i).put("HKSF", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKSF").toString()));
            } catch (Exception e) {
                result.get(i).put("HKSF", "");
            }
            try {
                result.get(i).put("HKQX", DataDict.getChineseValueByCode(EStandTables.省份城市表, result.get(i).get("HKQX").toString()));
            } catch (Exception e) {
                result.get(i).put("HKQX", "");
            }
            try {
                result.get(i).put("RYYJ", DataDict.getChineseValueByCode(EStandTables.入园依据表, result.get(i).get("RYYJ").toString()));
            } catch (Exception e) {
                result.get(i).put("RYYJ", "");
            }
            try {
                result.get(i).put("ZFXZ", DataDict.getChineseValueByCode(EStandTables.住房性质表, result.get(i).get("ZFXZ").toString()));
            } catch (Exception e) {
                result.get(i).put("ZFXZ", "");
            }
            try {
                result.get(i).put("DJLX", DataDict.getChineseValueByCode(EStandTables.登记类型表, result.get(i).get("DJLX").toString()));
            } catch (Exception e) {
                result.get(i).put("DJLX", "");
            }
            try {
                result.get(i).put("LQZT", DataDict.getChineseValueByCode(EStandTables.录取状态表, result.get(i).get("LQZT").toString()));
            } catch (Exception e) {
                result.get(i).put("LQZT", "");
            }
            try {
                result.get(i).put("CHECKSTATUS", DataDict.getChineseValueByCode(EStandTables.校验状态表, result.get(i).get("CHECKSTATUS").toString()));
            } catch (Exception e) {
                result.get(i).put("CHECKSTATUS", "");
            }
        }
        eh.setCurrentData(result);
        eh.writeCurrentData();
        result.add(filePath);
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url", virPath);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("导出成功", urlMap);
        return rs;
    }


    /**
     * 录取-查看录取信息M7-20
     *
     * @param {id}
     * @return [djinfo:{ id,xm,xb,sfzlx,sfzjh,djxx,djsj,djlx,lqxx,lqzt,djxxmc,lqxxmc,lqsj},bminfo:[{bmxx},{bmsj}]]
     * by huamin
     */
    @Override
    public JSONObject queryluquListById(Map<String, Object> params) throws SQLException, BaseException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        String stuinfoTableName = loginInfo.getStuinfotablename();
        String selectViewName = getv_m720_LuquListById_hm(stuinfoTableName);
        QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
        qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
        qeuryAdmissionList.printSelf();
        try {
            qeuryAdmissionList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> djresult = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("djinfo", djresult.get(0));
        QueryParams bmList = QueryParams.createQueryParams("vb_m720_getBaomingSchool_hm");
        bmList.addQueryParams(Parameter.createParameter("bmid", params.get("id")));
        if ("3".equals(loginInfo.getRoletype())) {//学校
            bmList.addQueryParams(Parameter.createParameter("bmxx", loginInfo.getSchoolcode()));
        } else if ("2".equals(loginInfo.getRoletype())) {//区县

        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
        bmList.addQueryParams(Parameter.createParameter("deleted", "0"));
        bmList.addOrderColumns("bmsj asc");
        bmList.printSelf();
        try {
            bmList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> bmresult = baseDaoComponent.selectDataByParams(bmList);
        data.put("bminfo", bmresult);
        JSONObject rs = createSuccessResponseBody("查看信息成功", data);
        return rs;
    }

    //* 录取M7-21
    //* @param {id}
    //* @return{}
    @Override
    public JSONObject setLuquById(Map<String, Object> params) throws SQLException, BaseException, ParseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
        if (commonService.checkDate(params)) {//判断是否打开录取通道
            loginInfo = (LoginInfo) params.remove("userinfo");
            String stuinfoTableName = loginInfo.getStuinfotablename();
            String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
            QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginInfo.getSchoolcode()));
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
            params.put("lqxx", loginInfo.getSchoolcode());
            try {
                qeuryAdmissionList.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
            String oldLqzt = "0";
            String checkStatus = "0";
            String oldsetlqzt = "0";
            if (baomingList.size() > 0) {
                oldLqzt = (String) baomingList.get(0).get("lqzt");
                checkStatus = (String) baomingList.get(0).get("checkstatus");
                oldsetlqzt = (String) baomingList.get(0).get("setlqzt");
                if ("1".equals(checkStatus)) {//校验通过才能操作
                    if ("0".equals(oldLqzt)) {//oldLqzt0为待录取
                        if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                            if ("1".equals(oldsetlqzt)) {
                                updateLqztById(loginInfo, params);
                            } else {
                                throw new BaseException("该学校没有操作权限");
                            }
                        } else {
                            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                        }
                    } else {
                        throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                    }
                } else {
                    throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
                }
            } else {
                throw new BaseException("该报名学校不存在该身份证号的学生");
            }
            rs = createSuccessResponseBody("录取成功");
        } else {
            loginInfo = (LoginInfo) params.remove("userinfo");
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
        }
        return rs;
    }

    //更新录取状态
    private int updateLqztById(LoginInfo loginInfo, Map<String, Object> params) throws SQLException, BaseException {
        if (getLuquinfoByStuid(loginInfo, (String) params.get("id"))) {//有数据
            UpdateParams updateParams = UpdateParams.createUpdateParams("t_luquinfo");
            updateParams.addWhereParameter(Parameter.createParameter("studentid", params.get("id")));
            updateParams.addWhereParameter(Parameter.createParameter("deleted", "0"));
            if (params.get("lqxx") != null && !"".equals(params.get("lqxx"))) {
                updateParams.addParams(Parameter.createParameter("lqxx", params.get("lqxx")));
                updateParams.addParams(Parameter.createParameter("lqqx", params.get("lqxx").toString().substring(0, 6)));
            } else {
                throw new BaseException("没有传入录取学校");
            }
            updateParams.addParam(Parameter.createParameter("lqzt", params.get("lqzt")));
            updateParams.addParam(Parameter.createParameter("lqsj", Format.getDateTime()));
            updateParams.addParam(Parameter.createParameter("updateuserid", loginInfo.getId()));
            updateParams.addParam(Parameter.createParameter("updatetime", Format.getDateTime()));
            try {
                updateParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            updateParams.printSelf();
            return baseDaoComponent.updateDataByParams(updateParams);
        } else {//无数据插入
            InsertParams insertParams = InsertParams.createInsertParams("t_luquinfo");
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("studentid", params.get("id"));
            map.put("deleted", "0");
            map.put("lqxx", params.get("lqxx"));
            map.put("lqqx", params.get("lqxx").toString().substring(0, 6));
            map.put("id", UUID.randomUUID().toString());
            map.put("lqzt", params.get("lqzt"));
            map.put("lqsj", Format.getDateTime());
            map.put("adduserid", loginInfo.getId());
            map.put("addtime", Format.getDateTime());
            insertParams.addParamsForMap(map);
            try {
                insertParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            baseDaoComponent.insertDataByParams(insertParams);
            return 1;
        }
    }

    //查询录取表是否有数据
    private boolean getLuquinfoByStuid(LoginInfo loginInfo, String stuid) throws SQLException {
        Boolean flag = false;//默认无数据
        QueryParams queryParams = QueryParams.createQueryParams("t_luquinfo");
        queryParams.addQueryParams(Parameter.createParameter("studentid", stuid));
        queryParams.addQueryParams(Parameter.createParameter("deleted", "0"));
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        if (result.size() > 0) {
            flag = true;
        }
        return flag;
    }

    //取消录取状态
    private int cancelLqztById(LoginInfo loginInfo, Map<String, Object> params) throws SQLException, BaseException {
        if (getLuquinfoByStuid(loginInfo, (String) params.get("id"))) {//有数据
            UpdateParams updateParams = UpdateParams.createUpdateParams("t_luquinfo");
            updateParams.addWhereParameter(Parameter.createParameter("studentid", params.get("id")));
            updateParams.addWhereParameter(Parameter.createParameter("deleted", "0"));
            updateParams.addParams(Parameter.createParameter("lqxx", ""));
            updateParams.addParams(Parameter.createParameter("lqqx", ""));
            updateParams.addParam(Parameter.createParameter("lqzt", params.get("lqzt")));
            updateParams.addParam(Parameter.createParameter("lqsj", ""));
            updateParams.addParam(Parameter.createParameter("updateuserid", loginInfo.getId()));
            updateParams.addParam(Parameter.createParameter("updatetime", Format.getDateTime()));
            try {
                updateParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return baseDaoComponent.updateDataByParams(updateParams);
        } else {//无数据插入
            InsertParams insertParams = InsertParams.createInsertParams("t_luquinfo");
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("studentid", params.get("id"));
            map.put("deleted", "0");
            map.put("lqxx", "");
            map.put("lqqx", "");
            map.put("id", UUID.randomUUID().toString());
            map.put("lqzt", params.get("lqzt"));
            map.put("lqsj", "");
            map.put("adduserid", loginInfo.getId());
            map.put("addtime", Format.getDateTime());
            insertParams.addParamsForMap(map);
            try {
                insertParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            baseDaoComponent.insertDataByParams(insertParams);
            return 1;
        }

    }

    //* 取消录取M7-22
    //* @param {id}
    //* @return{}
    @Override
    public JSONObject cancelLuquById(Map<String, Object> params) throws SQLException, BaseException, ParseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
        if (commonService.checkDate(params)) {
            //判断是否打开录取通道
            loginInfo = (LoginInfo) params.remove("userinfo");
            String stuinfoTableName = loginInfo.getStuinfotablename();
            String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
            QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginInfo.getSchoolcode()));
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
            try {
                qeuryAdmissionList.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
            String oldLqzt = "0";
            String checkStatus = "0";
            String oldsetlqzt = "0";
            if (baomingList.size() > 0) {
                oldLqzt = (String) baomingList.get(0).get("lqzt");
                checkStatus = (String) baomingList.get(0).get("checkstatus");
                oldsetlqzt = (String) baomingList.get(0).get("setlqzt");
                if ("1".equals(checkStatus)) {//校验通过才能操作
                    if ("1".equals(oldLqzt)) {//oldLqzt1为录取
                        if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                            if ("1".equals(oldsetlqzt)) {
                                cancelLqztById(loginInfo, params);
                            } else {
                                throw new BaseException("该学校没有操作权限");
                            }
                        } else {
                            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                        }
                    } else {
                        throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                    }
                } else {
                    throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
                }
            } else {
                throw new BaseException("该报名学校不存在该身份证号的学生");
            }
            rs = createSuccessResponseBody("取消录取成功");
        } else {
            loginInfo = (LoginInfo) params.remove("userinfo");
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
        }
        return rs;
    }

    //* 设置待统筹M7-23
    //        * @param {id}
    //* @return{}
    @Override
    public JSONObject setDaitongchouById(Map<String, Object> params) throws SQLException, BaseException, ParseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
        if (commonService.checkDate(params)) {
            //判断是否打开录取通道
            loginInfo = (LoginInfo) params.remove("userinfo");
            String stuinfoTableName = loginInfo.getStuinfotablename();
            String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
            QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginInfo.getSchoolcode()));
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
            try {
                qeuryAdmissionList.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
            String oldLqzt = "0";
            String checkStatus = "0";
            String oldsetdtczt = "0";
            if (baomingList.size() > 0) {
                oldLqzt = (String) baomingList.get(0).get("lqzt");
                checkStatus = (String) baomingList.get(0).get("checkstatus");
                oldsetdtczt = (String) baomingList.get(0).get("setdtczt");
                if ("1".equals(checkStatus)) {//校验通过才能操作
                    if ("0".equals(oldLqzt)) {//oldLqzt0为待录取
                        if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                            if ("1".equals(oldsetdtczt)) {
                                cancelLqztById(loginInfo, params);
                            } else {
                                throw new BaseException("该学校没有操作权限");
                            }
                        } else {
                            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                        }
                    } else {
                        throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                    }
                } else {
                    throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
                }
            } else {
                throw new BaseException("该报名学校不存在该身份证号的学生");
            }
            rs = createSuccessResponseBody("设置待统筹成功");
        } else {
                loginInfo = (LoginInfo) params.remove("userinfo");
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
        }
        return rs;
    }

    /**
     * 取消待统筹M7-24
     *
     * @param {id}
     * @return{} by huamin
     */
    @Override
    public JSONObject cancelDaitongchouById(Map<String, Object> params) throws SQLException, BaseException, ParseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
        if (commonService.checkDate(params)) {
            //判断是否打开录取通道
            loginInfo = (LoginInfo) params.remove("userinfo");
            String stuinfoTableName = loginInfo.getStuinfotablename();
            String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
            QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginInfo.getSchoolcode()));
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
            try {
                qeuryAdmissionList.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
            String oldLqzt = "0";
            String checkStatus = "0";
            String oldsetdtczt = "0";
            if (baomingList.size() > 0) {
                oldLqzt = (String) baomingList.get(0).get("lqzt");
                checkStatus = (String) baomingList.get(0).get("checkstatus");
                oldsetdtczt = (String) baomingList.get(0).get("setdtczt");
                if ("1".equals(checkStatus)) {//校验通过才能操作
                    if ("2".equals(oldLqzt)) {//oldLqzt2为待统筹
                        if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                            if ("1".equals(oldsetdtczt)) {
                                cancelLqztById(loginInfo, params);
                            } else {
                                throw new BaseException("该学校没有操作权限");
                            }
                        } else {
                            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                        }
                    } else {
                        throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                    }
                } else {
                    throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
                }
            } else {
                throw new BaseException("该报名学校不存在该身份证号的学生");
            }
            rs = createSuccessResponseBody("取消待统筹成功");
        } else {
                loginInfo = (LoginInfo) params.remove("userinfo");
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
        }
        return rs;
    }

    /**
     * 设置统筹录取M7-25
     *
     * @param {id,lqxx}
     * @return{} by huamin
     */
    @Override
    public JSONObject setTongchouLuquById(Map<String, Object> params) throws SQLException, BaseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
//        if (commonService.checkDate(params)) {
            //判断是否打开录取通道,区县暂时不用
            loginInfo = (LoginInfo) params.remove("userinfo");
        String stuinfoTableName = loginInfo.getStuinfotablename();
        String selectViewName = getv_m720_LuquListById_hm(stuinfoTableName);
        QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
        qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
        try {
            qeuryAdmissionList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
        String oldLqzt = "0";
        String checkStatus = "0";
        if (baomingList.size() > 0) {
            oldLqzt = (String) baomingList.get(0).get("lqzt");
            checkStatus = (String) baomingList.get(0).get("checkstatus");
            if ("1".equals(checkStatus)) {//校验通过才能操作
                if ("2".equals(oldLqzt)) {//oldLqzt2为待统筹
                    if ("2".equals(loginInfo.getRoletype())) {//只有区管能操作
                        updateLqztById(loginInfo, params);
                    } else {
                        throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                    }
                } else {
                    throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                }
            } else {
                throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
            }
        } else {
            throw new BaseException("该报名学校不存在该身份证号的学生");
        }
        rs = createSuccessResponseBody("设置统筹录取成功");
//        } else {
// loginInfo = (LoginInfo) params.remove("userinfo");
//            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode())+"录取通道未打开,无法操作");
//        }
        return rs;
    }

    /**
     * 统筹录取学校列表M7-251
     *
     * @param {id}
     * @return{lqxx,lqxxmc} by huamin
     */
    public JSONObject getTongchouLuquSchoolList(Map<String, Object> params) throws SQLException, BaseException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        QueryParams qeurySchoolList = QueryParams.createQueryParams("vb_m7251_getLuquSchoolList_hm");
        if ("2".equals(loginInfo.getRoletype())) {
            qeurySchoolList.addQueryParams(Parameter.createParameter("districtcode", loginInfo.getDistrictcode()));
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
        QueryParams qeuryBaomingList = QueryParams.createQueryParams("t_baominginfo");
        qeuryBaomingList.addQueryParams(Parameter.createParameter("deleted", "0"));
        qeuryBaomingList.addQueryParams(Parameter.createParameter("bmid", params.get("id")));
        try {
            qeuryBaomingList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryBaomingList);
        String bmxxList = "";
        for (int i = 0; i < baomingList.size(); i++) {
            if (i != baomingList.size() - 1) {
                bmxxList += baomingList.get(i).get("bmxx").toString() + ",";
            } else {
                bmxxList += baomingList.get(i).get("bmxx").toString();
            }
        }
        qeurySchoolList.addQueryParams(Parameter.createParameter("lqxx", EOperators.不包含, bmxxList.split(",")));
        qeurySchoolList.printSelf();
        try {
            qeurySchoolList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(qeurySchoolList);
        JSONObject rs = createSuccessResponseBody("学校列表查看成功", result);
        return rs;
    }

    /**
     * 取消统筹录取M7-26
     *
     * @param {id}
     * @return{} by huamin
     */
    @Override
    public JSONObject cancelTongchouLuquById(Map<String, Object> params) throws SQLException, BaseException {
        JSONObject rs = null;
        LoginInfo loginInfo = null;
        params.put("opentype", "2");//录取
//        if (commonService.checkDate(params)) {
            //判断是否打开录取通道,区县不需要操作
            loginInfo = (LoginInfo) params.remove("userinfo");
        String stuinfoTableName = loginInfo.getStuinfotablename();
        String selectViewName = getv_m720_LuquListById_hm(stuinfoTableName);
        QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
        qeuryAdmissionList.addQueryParams(Parameter.createParameter("id", params.get("id")));
        try {
            qeuryAdmissionList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
        String oldLqzt = "0";
        String checkStatus = "0";
        if (baomingList.size() > 0) {
            oldLqzt = (String) baomingList.get(0).get("lqzt");
            checkStatus = (String) baomingList.get(0).get("checkstatus");
            if ("1".equals(checkStatus)) {//校验通过才能操作
                if ("3".equals(oldLqzt)) {//oldLqzt3为统筹录取
                    if ("2".equals(loginInfo.getRoletype())) {//只有区管能操作
                        cancelLqztById(loginInfo, params);
                    } else {
                        throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                    }
                } else {
                    throw new BaseException("该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                }
            } else {
                throw new BaseException("该生尚未校验通过，不能进行录取相关操作");
            }
        } else {
            throw new BaseException("该报名学校不存在该身份证号的学生");
        }
        rs = createSuccessResponseBody("取消统筹录取成功");
//        } else {
        loginInfo = (LoginInfo) params.remove("userinfo");
//            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode())+"录取通道未打开,无法操作");
//        }
        return rs;
    }

    //    /**
    //     * 批量录取M7-31
    //     * @param {ids[],lqzt(1录取)}
    //     * @return{}
    //     *  by huamin
    //     */
    @Override
    public JSONObject setLuquByIdList(Map<String, Object> params, LoginInfo loginInfo) throws SQLException, BaseException, IOException, ParseException {
        MultipartFile file = (MultipartFile) params.get("file");
        String loginSchoolcode = loginInfo.getSchoolcode();
        params.remove("file");
        //获取文件保存路径
        FileInfoPo infoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.IMPORT_TYPE, "xlsx", "批量录取");
        String absPath = infoPo.getFileAbsPath();
        //1.0:生成本地文件
        FileUtils.writeByteArrayToFile(new File(absPath), file.getBytes());
        //1.1:校验Excel表格数据 (成功数据返回successList,错误数据返回errorList)
        String allHead = "{BMXX:学校代码,XM:姓名,SFZJH:证件号码}";
        List<Map<String, Object>> successList = new ArrayList<>();
        List<Map<String, Object>> errorList = new ArrayList<>();
        ExportExcel excelHelper = ExportExcel.createExportExcel(absPath, allHead);
        List<Map<String, Object>> excelList = null;
        excelList = excelHelper.readExcel();
        for (Map<String, Object> item : excelList) {
            //副本
            Map<String, Object> newMap = CollectionUtils.cloneMapData(item);
            params.put("opentype", "2");//录取
            params.put("userinfo",loginInfo);
            if (commonService.checkDate(params)) {
                //判断是否打开录取通道
                String stuinfoTableName = loginInfo.getStuinfotablename();
                String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
                QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
                String sfzjh = "";
                if (item.get("SFZJH") != null) {
                    sfzjh = item.get("SFZJH").toString().trim();
                } else {
                    newMap.put("remark", "证件号码不能为空");
                    errorList.add(newMap);
                    continue;
                }
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("sfzjh", sfzjh));
                String bxmm = "";
                if (item.get("BMXX") != null) {
                    bxmm = item.get("BMXX").toString().trim();
                }
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginSchoolcode));
                params.put("lqxx", loginInfo.getSchoolcode());
                try {
                    qeuryAdmissionList.setDbLog(loginInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
                String oldLqzt = "0";
                String oldXm = "";
                String checkStatus = "0";
                String oldId = "";
                String oldsetlqzt = "0";
                if (baomingList.size() > 0) {
                    oldLqzt = (String) baomingList.get(0).get("lqzt");
                    oldXm = (String) baomingList.get(0).get("xm");
                    oldId = (String) baomingList.get(0).get("id");
                    checkStatus = (String) baomingList.get(0).get("checkstatus");
                    oldsetlqzt = (String) baomingList.get(0).get("setlqzt");
                    if ("1".equals(checkStatus)) {//校验通过才能操作
                        if ("0".equals(oldLqzt)) {//oldLqzt0为待录取
                            if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                                if ("1".equals(oldsetlqzt)) {
                                    if (!loginSchoolcode.equals(bxmm)) {
                                        newMap.put("remark", "报名学校和登录学校不一致");
                                        errorList.add(newMap);
                                        continue;
                                    }
                                    String xm = null;
                                    if (item.get("xm") != null) {
                                        xm = item.get("xm").toString().trim();
                                    }
                                    if (!oldXm.equals(xm)) {
                                        newMap.put("remark", "姓名和该生信息中的姓名不一致");
                                        errorList.add(newMap);
                                        continue;
                                    }
                                    params.remove("id");
                                    params.put("id", oldId);
                                    int total = updateLqztById(loginInfo, params);
                                    if (total == 0) {
                                        newMap.put("remark", "该生录取状态未更新成功，请联系管理员");
                                        errorList.add(newMap);
                                    } else {
                                        successList.add(newMap);
                                    }
                                } else {
                                    throw new BaseException("该学校没有操作权限");
                                }
                            } else {
                                newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                                errorList.add(newMap);
                                continue;
                            }
                        } else {
                            newMap.put("remark", "该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                            errorList.add(newMap);
                            continue;
                        }
                    } else {
                        newMap.put("remark", "该生尚未校验通过，不能进行录取相关操作");
                        errorList.add(newMap);
                        continue;
                    }
                } else {
                    newMap.put("remark", "该报名学校不存在该身份证号的学生");
                    errorList.add(newMap);
                    continue;
                }
            } else {
                newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
                errorList.add(newMap);
                continue;
            }
        }
        //1.4:将成功的数据和失败的数据分别写入excel中
        String errorHead = "{BMXX:学校代码,XM:姓名,SFZJH:证件号码,REMARK:错误消息}";
        String url1 = writeToSuccessExcel(successList, allHead, "批量录取");
        String url2 = writeToErrorExcel(errorList, errorHead, "批量录取");
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url1", url1);
        urlMap.put("url2", url2);
        JSONObject rs = createSuccessResponseBody("共" + (successList.size() + errorList.size()) + "条数据，执行成功" + successList.size() + "条，失败" + errorList.size() + "条", urlMap);
        return rs;
    }

    /**
     * 批量设置待统筹M7-32
     *
     * @param {}
     * @return{} by huamin
     */
    @Override
    public JSONObject setDaitongchouByIdList(Map<String, Object> params, LoginInfo loginInfo) throws SQLException, BaseException, IOException, ParseException {
        MultipartFile file = (MultipartFile) params.get("file");
        String loginSchoolcode = loginInfo.getSchoolcode();
        params.remove("file");
        //获取文件保存路径
        FileInfoPo infoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.IMPORT_TYPE, "xlsx", "批量设置待统筹");
        String absPath = infoPo.getFileAbsPath();
        //1.0:生成本地文件
        FileUtils.writeByteArrayToFile(new File(absPath), file.getBytes());
        //1.1:校验Excel表格数据 (成功数据返回successList,错误数据返回errorList)
        String allHead = "{BMXX:学校代码,XM:姓名,SFZJH:证件号码}";
        List<Map<String, Object>> successList = new ArrayList<>();
        List<Map<String, Object>> errorList = new ArrayList<>();
        ExportExcel excelHelper = ExportExcel.createExportExcel(absPath, allHead);
        List<Map<String, Object>> excelList = null;
        excelList = excelHelper.readExcel();
        for (Map<String, Object> item : excelList) {
            //副本
            Map<String, Object> newMap = CollectionUtils.cloneMapData(item);
            params.put("opentype", "2");//录取
            params.put("userinfo",loginInfo);
            if (commonService.checkDate(params)) {
                //判断是否打开录取通道
                String stuinfoTableName = loginInfo.getStuinfotablename();
                String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
                QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
                String sfzjh = "";
                if (item.get("SFZJH") != null) {
                    sfzjh = item.get("SFZJH").toString().trim();
                } else {
                    newMap.put("remark", "证件号码不能为空");
                    errorList.add(newMap);
                    continue;
                }
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("sfzjh", sfzjh));
                String bxmm = "";
                if (item.get("BMXX") != null) {
                    bxmm = item.get("BMXX").toString().trim();
                }
                qeuryAdmissionList.addQueryParams(Parameter.createParameter("bmxx", loginSchoolcode));
                try {
                    qeuryAdmissionList.setDbLog(loginInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
                String oldLqzt = "0";
                String checkStatus = "0";
                String oldDjxx = "";
                String oldXm = "";
                String oldId = "";
                String oldsetdtczt = "0";
                if (baomingList.size() > 0) {
                    oldLqzt = (String) baomingList.get(0).get("lqzt");
                    checkStatus = (String) baomingList.get(0).get("checkstatus");
                    oldXm = (String) baomingList.get(0).get("xm");
                    oldId = (String) baomingList.get(0).get("id");
                    oldsetdtczt = (String) baomingList.get(0).get("setdtczt");
                    if ("1".equals(checkStatus)) {//校验通过才能操作
                        if ("0".equals(oldLqzt)) {//oldLqzt0为待录取
                            if ("3".equals(loginInfo.getRoletype())) {//只有学校能操作
                                if ("1".equals(oldsetdtczt)) {
                                    if (!loginSchoolcode.equals(bxmm)) {
                                        newMap.put("remark", "报名学校和登录学校不一致");
                                        errorList.add(newMap);
                                        continue;
                                    }
                                    String xm = null;
                                    if (item.get("xm") != null) {
                                        xm = item.get("xm").toString().trim();
                                    }
                                    if (!oldXm.equals(xm)) {
                                        newMap.put("remark", "姓名和该生信息中的姓名不一致");
                                        errorList.add(newMap);
                                        continue;
                                    }
                                    params.remove("id");
                                    params.put("id", oldId);
                                    int total = cancelLqztById(loginInfo, params);
                                    if (total == 0) {
                                        newMap.put("remark", "该生录取状态未更新成功，请联系管理员");
                                        errorList.add(newMap);
                                    } else {
                                        successList.add(newMap);
                                    }
                                } else {
                                    newMap.put("remark", "该学校没有操作权限");
                                    errorList.add(newMap);
                                    continue;
                                }
                            } else {
                                newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                                errorList.add(newMap);
                                continue;
                            }
                        } else {
                            newMap.put("remark", "该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                            errorList.add(newMap);
                            continue;
                        }
                    } else {
                        newMap.put("remark", "该生尚未校验通过，不能进行录取相关操作");
                        errorList.add(newMap);
                        continue;
                    }
                } else {
                    newMap.put("remark", "该报名学校不存在该身份证号的学生");
                    errorList.add(newMap);
                    continue;
                }
            } else {
                newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "录取通道未打开,无法操作");
                errorList.add(newMap);
                continue;
            }

        }
        //1.4:将成功的数据和失败的数据分别写入excel中
        String errorHead = "{BMXX:学校代码,XM:姓名,SFZJH:证件号码,REMARK:错误消息}";
        String url1 = writeToSuccessExcel(successList, allHead, "批量设置待统筹");
        String url2 = writeToErrorExcel(errorList, errorHead, "批量设置待统筹");
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url1", url1);
        urlMap.put("url2", url2);
        JSONObject rs = createSuccessResponseBody("共" + (successList.size() + errorList.size()) + "条数据，执行成功" + successList.size() + "条，失败" + errorList.size() + "条", urlMap);
        rs.put("url1", url1);
        rs.put("url2", url2);
        return rs;
    }

    /**
     * 批量统筹录取M7-33
     *
     * @param {}
     * @return{} by huamin
     */
    @Override
    public JSONObject setTongchouLuquByIdList(Map<String, Object> params, LoginInfo loginInfo) throws SQLException, BaseException, IOException, ParseException {
        MultipartFile file = (MultipartFile) params.get("file");
        params.remove("file");
        //获取文件保存路径
        FileInfoPo infoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.IMPORT_TYPE, "xlsx", "批量统筹录取");
        String absPath = infoPo.getFileAbsPath();
        //1.0:生成本地文件
        FileUtils.writeByteArrayToFile(new File(absPath), file.getBytes());
        //1.1:校验Excel表格数据 (成功数据返回successList,错误数据返回errorList)
        String allHead = "{DJXX:登记学校代码,XM:姓名,SFZJH:证件号码,LQXX:统筹录取学校代码}";
        List<Map<String, Object>> successList = new ArrayList<>();
        List<Map<String, Object>> errorList = new ArrayList<>();
        ExportExcel excelHelper = ExportExcel.createExportExcel(absPath, allHead);
        List<Map<String, Object>> excelList = null;
        excelList = excelHelper.readExcel();
        for (Map<String, Object> item : excelList) {
            //副本
            Map<String, Object> newMap = CollectionUtils.cloneMapData(item);
//            params.put("opentype", "2");//录取
//            params.put("userinfo",loginInfo);
//            if (commonService.checkDate(params)) {
                //判断是否打开录取通道
            String stuinfoTableName = loginInfo.getStuinfotablename();
            String selectViewName = getv_m720_bmlqlistbyid_hm(stuinfoTableName);
            QueryParams qeuryAdmissionList = QueryParams.createQueryParams(selectViewName);
            String sfzjh = "";
            if (item.get("SFZJH") != null) {
                sfzjh = item.get("SFZJH").toString().trim();
            } else {
                newMap.put("remark", "证件号码不能为空");
                errorList.add(newMap);
                continue;
            }
            String djxx = "";
            if (item.get("DJXX") != null) {
                djxx = item.get("DJXX").toString().trim();
            } else {
                newMap.put("remark", "登记学校不能为空");
                errorList.add(newMap);
                continue;
            }
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("sfzjh", sfzjh));
            qeuryAdmissionList.addQueryParams(Parameter.createParameter("djxx", djxx));
            try {
                qeuryAdmissionList.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> baomingList = baseDaoComponent.selectDataByParams(qeuryAdmissionList);
            String oldLqzt = "0";
            String checkStatus = "0";
            String oldXm = "";
            String oldId = "";
            if (baomingList.size() > 0) {
                oldLqzt = (String) baomingList.get(0).get("lqzt");
                checkStatus = (String) baomingList.get(0).get("checkstatus");
                oldXm = (String) baomingList.get(0).get("xm");
                oldId = (String) baomingList.get(0).get("id");
                if ("1".equals(checkStatus)) {//校验通过才能操作
                    if ("2".equals(oldLqzt)) {//oldLqzt2为待统筹
                        if ("2".equals(loginInfo.getRoletype())) {//只有区管能操作
                            String xm = null;
                            if (item.get("xm") != null) {
                                xm = item.get("xm").toString().trim();
                            }
                            if (!oldXm.equals(xm)) {
                                newMap.put("remark", "姓名和该生信息中的姓名不一致");
                                errorList.add(newMap);
                                continue;
                            }
                            String lqxx = null;
                            if (item.get("lqxx") != null) {
                                lqxx = item.get("lqxx").toString().trim();
                            }
                            if (checkStuLqxx(loginInfo,lqxx,oldId)) {
                                newMap.put("remark", "该生在该录取学校已报名，无法做统筹录取操作");
                                errorList.add(newMap);
                                continue;
                            }
                            params.remove("id");
                            params.put("id", oldId);
                            params.remove("lqxx");
                            params.put("lqxx", lqxx);
                            int total = updateLqztById(loginInfo, params);
                            if (total == 0) {
                                newMap.put("remark", "该生录取状态未更新成功，请联系管理员");
                                errorList.add(newMap);
                            } else {
                                successList.add(newMap);
                            }
                        } else {
                            newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
                            errorList.add(newMap);
                            continue;
                        }
                    } else {
                        newMap.put("remark", "该生的录取状态为" + DataDict.getChineseValueByCode(EStandTables.录取状态表, oldLqzt) + "，无法操作");
                        errorList.add(newMap);
                        continue;
                    }
                } else {
                    newMap.put("remark", "该生的录取状态为" + "该生尚未校验通过，不能进行录取相关操作");
                    errorList.add(newMap);
                    continue;
                }
            } else {
                newMap.put("remark", "该登记学校不存在该身份证号的学生");
                errorList.add(newMap);
                continue;
            }
//            } else {
//                newMap.put("remark", DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode())+"录取通道未打开,无法操作");
//                errorList.add(newMap);
//                continue;
//            }
        }
        //1.4:将成功的数据和失败的数据分别写入excel中
        String errorHead = "{DJXX:登记学校代码,XM:姓名,SFZJH:证件号码,LQXX:统筹录取学校代码,REMARK:错误消息}";
        String url1 = writeToSuccessExcel(successList, allHead, "批量统筹录取");
        String url2 = writeToErrorExcel(errorList, errorHead, "批量统筹录取");
        Map<String, Object> urlMap = new HashMap<>();
        urlMap.put("url1", url1);
        urlMap.put("url2", url2);
        JSONObject rs = createSuccessResponseBody("共" + (successList.size() + errorList.size()) + "条数据，执行成功" + successList.size() + "条，失败" + errorList.size() + "条", urlMap);
        rs.put("url1", url1);
        rs.put("url2", url2);
        return rs;
    }

    //检查学生录取学校是否在该生的报名
    private boolean checkStuLqxx(LoginInfo loginInfo,String lqxx,String stuid) throws SQLException {
        Boolean flag=false;
        QueryParams queryParams = QueryParams.createQueryParams("t_baominginfo");
        queryParams.addQueryParams(Parameter.createParameter("bmid", stuid));
        queryParams.addQueryParams(Parameter.createParameter("deleted", "0"));
        queryParams.addQueryParams(Parameter.createParameter("bmxx", lqxx));
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        if(result.size()>0){
            flag=true;
        }
        return flag;
    }

    /**
     * 写入成功信息表格
     **/
    public String writeToSuccessExcel(List<Map<String, Object>> list, String head, String excelName) throws IOException, FileTypeNotFoundException {
        FileInfoPo info = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, ".xlsx", "操作成功的数据");
        String absPath = info.getFileAbsPath();
        String virPath = info.getFileVirPath();
        ExportExcel exportExcel = ExportExcel.createExportExcel(absPath, head);
        exportExcel.setCurrentData(list);
        exportExcel.writeCurrentData();
        return virPath;
    }

    /**
     * 写入错误信息表格
     **/
    public String writeToErrorExcel(List<Map<String, Object>> list, String head, String excelName) throws IOException, FileTypeNotFoundException {
        FileInfoPo info = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, ".xlsx", "操作失败的数据");
        String absPath = info.getFileAbsPath();
        String virPath = info.getFileVirPath();
        ExportExcel exportExcel = ExportExcel.createExportExcel(absPath, head);
        exportExcel.setCurrentData(list);
        exportExcel.writeCurrentData();
        return virPath;
    }

    /**
     * 生成待统筹学生上报表M7-4
     *
     * @param {}
     * @return []
     * by huamin
     */
    public JSONObject expDaiTongchouSbReport(Map<String, Object> params, LoginInfo loginInfo) throws SQLException, BaseException, IOException {
        params.remove("limit");
        params.remove("offset");
        params.put("lqzt", "2");
        List<Map<String, Object>> result = queryXXDJXSListMain(params);
        // 年级，姓名，性别，证件类型，证件号码，出生日期，户口省份，户口区县，入园依据，住房性质，登记状态，录取状态
        // ID,NJDM,XM,XB,SFZLX,SFZJH,CSRQ,HKSF,HKQX,RYYJ,ZFXZ,DJLX,LQZT,CHECKSTATUS
        //NUMBER:序号,NJDM:年级,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,HKSF:户口省份,HKQX:户口区县,RYYJ:入园依据,ZFXZ:住房性质,DJLX:登记状态,LQZT:录取状态,CHECKSTATUS:校验状态
        FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE, "xlsx", "生成待统筹学生上报表");
        String absPath = fileInfoPo.getFileAbsPath();
        String virPath = fileInfoPo.getFileVirPath();
        String schoolname = getSchoolName(loginInfo);
        Map<String, Object> filePath = new HashMap<>();
        filePath.put("url", virPath);
        ExportExcel eh = ExportExcel.createExportExcel(absPath, "{NUMBER:序号,XM:姓名,XB:性别,SFZLX:证件类型,SFZJH:证件号码,CSRQ:出生日期,NJDM:年级}");
        for (int i = 0, len = result.size(); i < len; i++) {
            result.get(i).put("NUMBER", i + 1);
            try {
                result.get(i).put("NJDM", DataDict.getChineseValueByCode(EStandTables.年级表, result.get(i).get("NJDM").toString()));
            } catch (Exception e) {
                result.get(i).put("NJDM", "");
            }
            try {
                result.get(i).put("XB", DataDict.getChineseValueByCode(EStandTables.性别表, result.get(i).get("XB").toString()));
            } catch (Exception e) {
                result.get(i).put("XB", "");
            }
            try {
                result.get(i).put("SFZLX", DataDict.getChineseValueByCode(EStandTables.证件类型表, result.get(i).get("SFZLX").toString()));
            } catch (Exception e) {
                result.get(i).put("SFZLX", "");
            }
            try {
                String csrq1 = result.get(i).get("CSRQ").toString().substring(0, 4);
                String csrq2 = result.get(i).get("CSRQ").toString().substring(4, 6);
                String csrq3 = result.get(i).get("CSRQ").toString().substring(6, 8);
                result.get(i).put("CSRQ", csrq1 + "-" + csrq2 + "-" + csrq3);
            } catch (Exception e) {
                result.get(i).put("CSRQ", "");
            }
        }
        eh.setCurrentData(result);
        eh.writeExpDaiTongchouSbReport(schoolname);
        Map<String, Object> data = new HashMap<>();
        data.put("url", fileInfoPo.getFileVirPath());
        JSONObject rs2 = ResponseUtils.createSuccessResponseBody("操作成功", data);
        return rs2;
    }

    //获得学校名称
    private String getSchoolName(LoginInfo loginInfo) throws SQLException, BaseException {
        QueryParams qeurySchoolList = QueryParams.createQueryParams("t_schoolinfo");
        qeurySchoolList.addQueryParams(Parameter.createParameter("schoolcode", loginInfo.getSchoolcode()));
        qeurySchoolList.addQueryParams(Parameter.createParameter("deleted", "0"));
        qeurySchoolList.printSelf();
        try {
            qeurySchoolList.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(qeurySchoolList);
        String schoolname = "";
        if (result.size() > 0) {
            schoolname = (String) result.get(0).get("schoolname");
        } else {
            throw new BaseException(loginInfo.getSchoolcode() + "该学校不存在");
        }
        return schoolname;
    }

    /**
     * 生成单个录取通知书M7-51
     *
     * @param {id}
     * @return []
     * by huamin
     */
    public JSONObject expluquNoticeById(Map<String, Object> params) throws SQLException, BaseException, IOException, DocumentException, ParseException {
        //生成单个录取通知书M7-51
        LoginInfo loginInfo = (LoginInfo) params.get("userinfo");
        JSONObject rs = null;
        if ("3".equals(loginInfo.getRoletype())) {//只有学校有权限
            params.put("opentype", "3");//生成录取通知书
            if (commonService.checkDate(params)) {
                //判断是否打开生成录取通知书通道
                List<Map<String, Object>> mapList = queryXXLuquListMain(params);
                Map<String, Object> schoolInfo = querySchoolInfo(loginInfo.getSchoolcode(), loginInfo);
                if (mapList.isEmpty()) throw new BaseException("没有数据");
                //生成录取通知书
                String url = createExpluquNotice(mapList, schoolInfo); //返回下载地址
                //封装参数
                Map<String, Object> map = new HashMap<>();
                map.put("url", url);
                rs = ResponseUtils.createSuccessResponseBody("生成成功", map);
                return rs;
            } else {
                throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "生成录取通知书通道处于打开中,不能进行此操作");
            }
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
    }

    /**
     * 生成全部录取通知书M7-52
     *
     * @param {}
     * @return []
     * by huamin
     */
    public JSONObject expluquNoticeAll(Map<String, Object> params) throws SQLException, BaseException, IOException, DocumentException, ParseException {
        //生成全部录取通知书M7-52
        LoginInfo loginInfo = (LoginInfo) params.get("userinfo");
        JSONObject rs = null;
        if ("3".equals(loginInfo.getRoletype())) {//只有学校有权限
            params.put("opentype", "3");//生成录取通知书
            if (commonService.checkDate(params)) {
                //判断是否打开生成录取通知书通道
                List<Map<String, Object>> mapList = queryXXLuquListMain(params);
                Map<String, Object> schoolInfo = querySchoolInfo(loginInfo.getSchoolcode(), loginInfo);
                if (mapList.isEmpty()) throw new BaseException("没有数据");
                //生成录取通知书
                String url = createExpluquNotice(mapList, schoolInfo); //返回下载地址
                //封装参数
                Map<String, Object> map = new HashMap<>();
                map.put("url", url);
                rs = ResponseUtils.createSuccessResponseBody("生成成功", map);
                return rs;
            } else {
                throw new BaseException(DataDict.getChineseValueByCode(EStandTables.区县表, loginInfo.getDistrictcode()) + "生成录取通知书通道处于打开中,不能进行此操作");
            }
        } else {
            throw new BaseException(DataDict.getChineseValueByCode(EStandTables.角色类型, loginInfo.getRoletype()) + "账户无权限操作");
        }
    }

    //查询学校信息
    private Map<String, Object> querySchoolInfo(String schoolcode, LoginInfo loginInfo) throws SQLException {
        QueryParams querySchoolCode = QueryParams.createQueryParams("t_schoolinfo");
        querySchoolCode.addQueryParams(Parameter.createParameter("schoolcode", schoolcode));
        querySchoolCode.addQueryParams(Parameter.createParameter("deleted", "0"));
        try {
            querySchoolCode.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> mapList = baseDaoComponent.selectDataByParams(querySchoolCode);
        return mapList.get(0);
    }

    //按区查视图v_m711_xxdjxslist_hm
    private String getv_m711_xxdjxslist_hm(String stuinfoTableName) throws BaseException {
        String selectViewName = "";
        if (stuinfoTableName != null && !"".equals(stuinfoTableName)) {
            if ("T_ZHK_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m711_xxdjxslist_hm_310109";
            } else if ("T_ZJS_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m711_xxdjxslist_hm_310116";
            } else {
                throw new BaseException("stuinfoTableName关联表出错，查询不到关系视图");
            }
        } else {
            throw new BaseException("stuinfoTableName关联表出错");
        }
        return selectViewName;
    }

    //按区查视图v_m712_XXLuquList_hm
    private String getv_m712_XXLuquList_hm(String stuinfoTableName) throws BaseException {
        String selectViewName = "";
        if (stuinfoTableName != null && !"".equals(stuinfoTableName)) {
            if ("T_ZHK_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m712_XXLuquList_hm_310109";
            } else if ("T_ZJS_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m712_XXLuquList_hm_310116";
            } else {
                throw new BaseException("stuinfoTableName关联表出错，查询不到关系视图");
            }
        } else {
            throw new BaseException("stuinfoTableName关联表出错");
        }
        return selectViewName;
    }

    //按区查视图v_m713_qxluqulist_hm
    private String getv_m713_qxluqulist_hm(String stuinfoTableName) throws BaseException {
        String selectViewName = "";
        if (stuinfoTableName != null && !"".equals(stuinfoTableName)) {
            if ("T_ZHK_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m713_qxluqulist_hm_310109";
            } else if ("T_ZJS_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m713_qxluqulist_hm_310116";
            } else {
                throw new BaseException("stuinfoTableName关联表出错，查询不到关系视图");
            }
        } else {
            throw new BaseException("stuinfoTableName关联表出错");
        }
        return selectViewName;
    }

    //按区查视图v_m720_LuquListById_hm
    private String getv_m720_LuquListById_hm(String stuinfoTableName) throws BaseException {
        String selectViewName = "";
        if (stuinfoTableName != null && !"".equals(stuinfoTableName)) {
            if ("T_ZHK_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m720_LuquListById_hm_310109";
            } else if ("T_ZJS_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m720_LuquListById_hm_310116";
            } else {
                throw new BaseException("stuinfoTableName关联表出错，查询不到关系视图");
            }
        } else {
            throw new BaseException("stuinfoTableName关联表出错");
        }
        return selectViewName;
    }

    //按区查视图v_m720_bmlqlistbyid_hm
    private String getv_m720_bmlqlistbyid_hm(String stuinfoTableName) throws BaseException {
        String selectViewName = "";
        if (stuinfoTableName != null && !"".equals(stuinfoTableName)) {
            if ("T_ZHK_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m720_bmlqlistbyid_hm_310109";
            } else if ("T_ZJS_STUINFO".equals(stuinfoTableName)) {
                selectViewName = "v_m720_bmlqlistbyid_hm_310116";
            } else {
                throw new BaseException("stuinfoTableName关联表出错，查询不到关系视图");
            }
        } else {
            throw new BaseException("stuinfoTableName关联表出错");
        }
        return selectViewName;
    }

}

